import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 第二次测试获取全部字符串
 */
public class TestRead_02 {
    public static void main(String[] args) throws IOException {
        String Path = "C:\\Users\\Administrator\\Desktop\\11.xlsx";
        String Path2 = "C:\\Users\\Administrator\\Desktop\\22.xlsx";

        List<String> list = readTest(Path);
        queryString(Path2,list);

    }

    /**
     * 获取所有字符串并存入list
     * @param Path
     * @return
     * @throws IOException
     */
    public static List<String> readTest(String Path) throws IOException {
        String cellValue = "";
        List<String> list = new ArrayList<String>();
        list.add("标题");
        FileInputStream file = null;
        ExcelPoiUtils excelPoiUtils = new ExcelPoiUtils();
        {
            try {
                file = new FileInputStream(Path);

                Workbook workbook = new XSSFWorkbook(file);

                Sheet sheet = workbook.getSheetAt(0);

                int rows = sheet.getLastRowNum();
                System.out.println("A表行共有" + rows);
//                System.out.println(rows);
                for (int row = 1; row <= rows; row++) {
                    Row thisrow = sheet.getRow(row);
                    if (thisrow == null){
                        System.out.println("A表第" + row + "行为空");
                        continue;
                    }else{
                        Cell thiscell = thisrow.getCell(19);

                        if (thiscell == null){
                            list.add("null");
                            System.out.println("存null,该表格没有值：（"+ row + ",19)");
                            continue;
                        }else{
                            cellValue = excelPoiUtils.getValue(thiscell);
                            list.add(cellValue);
//                        if (cellValue.length() >= 12){
////                            System.out.println(cellValue.substring(0, 12)+"__"+numAll);
//                            System.out.println(cellValue+"____"+row);
//                        }else{
//                            System.out.println("字符串长度不够或为空");
//                        }
                        }
                    }

                }
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }finally {
                file.close();
            }
        }
        return list;
    }

    /**
     * 到传来的第二文件查数据，并生成一个表
     * @param Path2
     * @param list
     * @throws IOException
     */
    public static void queryString(String Path2,List<String> list) throws IOException {
        FileInputStream file = null;
        String cellValue = "";
        String cellValueNext = "";
        int thisEnd = 0;
        String Path3 = "C:\\Users\\Administrator\\Desktop\\第三表格.xlsx";

        Workbook workbook2 = new XSSFWorkbook();

        Sheet sheet2 = workbook2.createSheet();

        Row titleRow = sheet2.createRow(0);
        Cell titleCell1 = titleRow.createCell(0);
        titleCell1.setCellValue("WBS 元素");
        Cell titleCell12 = titleRow.createCell(1);
        titleCell12.setCellValue("项目定义");
        Cell titleCell13 = titleRow.createCell(2);
        titleCell13.setCellValue("描述");
        Cell titleCell14 = titleRow.createCell(3);
        titleCell14.setCellValue("单位");

        ExcelPoiUtils excelPoiUtils = new ExcelPoiUtils();
        Workbook workbook = new XSSFWorkbook(new FileInputStream(Path2));
        Sheet sheet = workbook.getSheetAt(0);
        int rows = sheet.getPhysicalNumberOfRows();

        for (int i = 1; i < list.size(); i++) {//已补充位置，所以list从1开始
            if (list.get(i).equals("null")){
                System.out.println("list中为null的第" + i + "个");
                continue;
            }else{
                for (int row = 1; row <= rows; row++) {//总共有rows行提供数据
                    Row thisrow = sheet.getRow(row);
                    if (thisrow == null){
                        System.out.println("B表该行为空:" + row + "（这种事情应该不会发生，可能数据提供残缺）");
                    }else{
                        Cell thiscell = thisrow.getCell(0);
                        if (thiscell == null){
                            System.out.println("B表表格为没值:" + "(" + row + ",0)");
                        }else{
                            String thisvalue = excelPoiUtils.getValue(thiscell);

                            if (list.get(i).length() >= 12){//提取12位字符串,
                                if (thisvalue.equals(list.get(i).substring(0,12))){
//                System.out.println((row+1) + "__过程__" +thisvalue);
                                    cellValue = thisvalue;
                                    Cell cellEnd = thisrow.getCell(1);
                                    cellValueNext = excelPoiUtils.getValue(cellEnd);

                                    Row row2 = sheet2.createRow(i);
                                    Cell thisCell1 = row2.createCell(0);
                                    Cell thisCell2 = row2.createCell(1);
                                    Cell thisCell3 = row2.createCell(2);

                                    thisCell1.setCellValue(list.get(i));
                                    thisCell2.setCellValue(list.get(i).substring(0,12));
                                    thisCell3.setCellValue(cellValueNext);
                                }
                            }else{
                                System.out.println("字符串长度不足12个:"+ i );
                            }
                        }
                    }

                }
            }
        }
        FileOutputStream file2 = new FileOutputStream(Path3);
        workbook2.write(file2);
        file2.close();
        System.out.println("文件生成完毕");

    }

    /**
     *  根据kv 生成excel
     * @param Path3
     * @param firstKey
     * @param secondValue
     * @throws IOException
     */
    public static void exportExcel(String Path3,String firstKey,String secondValue) throws IOException {
        Workbook workbook = new XSSFWorkbook();

        Sheet sheet = workbook.createSheet();

        Row titleRow = sheet.createRow(0);
        Cell titleCell1 = titleRow.createCell(0);
        titleCell1.setCellValue("项目定义");
        Cell titleCell12 = titleRow.createCell(1);
        titleCell12.setCellValue("描述");
        Cell titleCell13 = titleRow.createCell(2);
        titleCell13.setCellValue("单位");

        Row thisRow = sheet.createRow(1);//第一行创建和赋值
        Cell thisCell1 = thisRow.createCell(0);
        Cell thisCell2 = thisRow.createCell(1);
        thisCell1.setCellValue(firstKey);
        thisCell2.setCellValue(secondValue);

        FileOutputStream file = new FileOutputStream(Path3);
        workbook.write(file);
        file.close();
        System.out.println("文件生成完毕");

    }


}
